﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using Vowei.Core.Models;
using System.Diagnostics;
using NPOI.HSSF.UserModel;
using System.Linq.Expressions;
using Vowei.Core;

namespace Vowei.Data.Converters.Excel
{
    public abstract class ExcelConverter<T> : FormatConverter
    {
        public string WorkSheetName
        {
            get;
            set;
        }
        
        public string TemplateExcel { get; set; }

        protected ExcelConverter(ITranslator translator) : base(translator) { }
        
        protected U ReadCell<U>(DataRow row, string name)
            where U : IConvertible
        {
            return ReadCell<U>(row, name, true);
        }

        protected U ReadCell<U>(DataRow row, string name, bool isRequired)
            where U : IConvertible
        {
            if (row.Table.Columns.Contains(name))
            {
                if (row[name] != DBNull.Value)
                    return (U)Convert.ChangeType(row[name], typeof(U), Translator.Culture);
            }
            else
                throw new InvalidDataException(string.Format(Translator.T("在试图导入的Excel文件里，找不到列名为“{0}”的列"), name));

            if (isRequired && (row[name] == DBNull.Value))
                throw new InvalidDataException(string.Format(Translator.T("在试图导入的Excel文件里，必填列“{0}”的值为空"), name));

            return default(U);
        }

        protected U? ReadNullablCell<U>(DataRow row, string name)
            where U : struct, IConvertible
        {
            if (row.Table.Columns.Contains(name))
            {
                if (row[name] != DBNull.Value)
                    return ReadCell<U>(row, name, true);
            }

            return null;
        }

        protected DateTime? ReadDateCell(Row row, int num)
        {
            var value = ReadNullablCell<double>(row, num);
            if (value.HasValue)
            {
                return DateTime.FromOADate(value.Value);
            }
            else
                return null;
        }

        protected U? ReadNullablCell<U>(Row row, int num)
            where U : struct, IConvertible
        {
            return (U?)ReadNullableCellImpl<U>(row, num);
        }

        protected Guid? ReadGuidCell(Row row, int num)
        {
            var value = ReadCell<string>(row, num, false);
            if (value != null)
            {
                return Guid.Parse(value);
            }
            else
                return null;
        }

        protected U ReadCell<U>(Row row, int num)
            where U : IConvertible
        {
            return ReadCell<U>(row, num, true);
        }

        protected U ReadCell<U>(Row row, int num, bool isRequired)
            where U : IConvertible
        {
            var result = ReadNullableCellImpl<U>(row, num);

            if (result == null && isRequired)
                throw new InvalidDataException(string.Format(Translator.T("在试图导入的Excel文件里，必填的第{0}列没有数据！"), num + 1));
            else if (result == null)
                return default(U);
            else
                return (U)result;
        }

        protected Dictionary<string, int> ReadColumnNames(Row row, string[] names)
        {
            return ReadColumnNames(row, names, row.FirstCellNum, row.LastCellNum);
        }

        class ColumnNameComparer : IComparer<string>, IEqualityComparer<string>
        {
            private CultureInfo _culture;
            public ColumnNameComparer(CultureInfo culture)
            {
                _culture = culture;
            }

            public int Compare(string x, string y)
            {
                if (x == y)
                    return 0;
                else if (x == null && y != null)
                    return -1;
                else if (x != null && y == null)
                    return 1;
                else
                {
                    return _culture == null ? string.Compare(x, y, true)
                                            : string.Compare(x, y, true, _culture);
                }
            }

            public bool Equals(string x, string y)
            {
                return _culture == null ? string.Compare(x, y, true) == 0
                                        : string.Compare(x, y, true, _culture) == 0;
            }

            public int GetHashCode(string obj)
            {
                return obj.GetHashCode();
            }
        }

        protected Dictionary<string, int> ReadColumnNames(Row row, int firstColumn, int lastColumn)
        {
            var result = new Dictionary<string, int>();
            for (int i = firstColumn; i < lastColumn; ++i)
            {
                var cell = row.GetCell(i);
                if (cell == null || cell.CellType != CellType.STRING)
                    break;

                var name = cell.StringCellValue;
                result.Add(name, i);
            }

            return result;
        }

        protected Dictionary<string, int> ReadColumnNames(Row row, string[] names, int firstColumn, int lastColumn)
        {
            Array.Sort(names);
            var columnNameComparer = new ColumnNameComparer(Translator.Culture);
            var result = new Dictionary<string, int>();
            for (int i = firstColumn; i < lastColumn; ++i)
            {
                var cell = row.GetCell(i);
                if (cell == null || cell.CellType != CellType.STRING)
                    break;

                var name = cell.StringCellValue;
                var idx = Array.BinarySearch(names, name, columnNameComparer);
                if (idx >= 0)
                {
                    result.Add(names[idx], i);
                }
            }

            if (result.Keys.Count != names.Length)
            {
                var diffs = names.Except(result.Keys, columnNameComparer);
                var builder = new StringBuilder();

                foreach (var diff in diffs)
                {
                    builder.Append(diff);
                    builder.Append(", ");
                }

                Trace.TraceError(string.Format(Translator.T("指定的Excel文件的{0}页签里，没有必要的列： {1}"), WorkSheetName, builder.ToString()));
            }

            for (int i = 0; i < names.Length; ++i)
            {
                var name = names[i];
                if (!result.ContainsKey(name))
                    result.Add(name, -1);
            }

            return result;
        }
        
        protected bool TryConvertBoolean(string value)
        {
            if (string.IsNullOrEmpty(value))
                return false;

            if (string.Compare("Y", value, true) == 0)
                return true;
            else if (string.Compare("N", value, true) == 0)
                return false;
            else if (string.Compare("Yes", value, true) == 0)
                return true;
            else if (string.Compare("No", value, true) == 0)
                return false;
            else
                return Convert.ToBoolean(value);
        }

        protected static Sheet GetWorkItemsSheet(HSSFWorkbook workbook, string sheetName)
        {
            // 如果没有指定要查询的工作表名，那么就使用第一个工作表
            Sheet sheet = null;
            if (!string.IsNullOrEmpty(sheetName))
                sheet = workbook.GetSheet(sheetName);

            if (sheet == null)
                sheet = workbook.GetSheetAt(0);

            if (sheet == null)
                throw new InvalidOperationException(string.Format("没有指定包含工作任务的工作表名：{0}，而且要导入的Excel文件也没有一个工作表！", sheetName));
            else
                return sheet;
        }

        protected Sheet GetWorkItemsSheet(HSSFWorkbook workbook)
        {
            return GetWorkItemsSheet(workbook, WorkSheetName);
        }

        protected int GuessFirstRow(Sheet sheet, string keyColumn, bool throwIt)
        {
            for (int i = sheet.FirstRowNum; i < sheet.LastRowNum; ++i)
            {
                var row = sheet.GetRow(i);
                if (row == null)
                    continue;

                for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
                {
                    var columnName = ReadCell<string>(row, j, false);
                    if (string.Compare(columnName, keyColumn, true) == 0)
                        return i;
                }
            }

            if (throwIt)
                throw new InvalidDataException(string.Format(Translator.T("在试图导入的Excel工作表[{0}]里，没有必填列{1}，以至于程序无法正确判断出数据起始的列号！"), sheet.SheetName, keyColumn));
            else
                return -1;            
        }

        protected int GuessFirstRow(Sheet sheet, string keyColumn)
        {
            return GuessFirstRow(sheet, keyColumn, true);
        }

        private object ReadNullableCellImpl<U>(Row row, int num)
            where U : IConvertible
        {
            // 如果用户已经指定num为负数，这说明是读取可选列。
            if (num < 0 || row == null)
                return null;

            var cell = row.GetCell(num);
            if (cell == null)
                return null;

            object value = null;

            if (typeof(U) == typeof(DateTime))
                value = cell.DateCellValue;
            else if (cell.CellType == CellType.STRING)
                value = cell.StringCellValue;
            else if (cell.CellType == CellType.NUMERIC)
                value = cell.NumericCellValue;
            else if (cell.CellType == CellType.FORMULA && cell.CachedFormulaResultType == CellType.NUMERIC)
                value = cell.NumericCellValue;
            else if (cell.CellType == CellType.FORMULA && cell.CachedFormulaResultType == CellType.STRING)
                value = cell.StringCellValue;

            if (value == null)
                return null;
            else
                return (U)Convert.ChangeType(value, typeof(U), Translator.Culture);
        }

        public override bool CanConvert(object stream)
        {
            if (!(stream is Stream))
                throw new ArgumentException("stream");

            return CanConvert(stream as Stream);
        }

        public abstract bool CanConvert(Stream stream);

        public abstract T ConvertFrom(Stream from);

        public override object ConvertFrom(object from)
        {
            if (!(from is Stream))
                throw new ArgumentException("from");

            return ConvertFrom(from as Stream);
        }

        public abstract void ConvertTo(T from, Stream to);

        public override void ConvertTo(object from, object to)
        {
            if (!(to is Stream))
                throw new ArgumentException("to");

            ConvertTo((T)from, to);
        }

        protected string FieldName<TProperty>(Expression<Func<Ticket, TProperty>> expression)
        {
            return Translator.T<Ticket, TProperty>(expression);
        }
    }
}
